<!DOCTYPE html>
<html>
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="utf-8">
  
  <title>mysql之count用法 | JAVA</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  <meta name="keywords" content="面试经典">
  
  
  
  
  <meta name="description" content="这个常用的COUNT函数，却暗藏着很多玄机，尤其是在面试的时候，一不小心就会被虐。不信的话请尝试回答下以下问题：  1、COUNT有几种用法？ 2、COUNT(字段名)和COUNT(*)的查询结果有什么不同？ 3、COUNT(1)和COUNT(*)之间有什么不同？ 4、COUNT(1)和COUNT(*)之间的效率哪个更高？ 5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*) 6、MyS">
<meta name="keywords" content="面试经典">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql之count用法">
<meta property="og:url" content="https://jameslin23.gitee.io/2020/12/10/mysql之count用法/index.html">
<meta property="og:site_name" content="JAVA">
<meta property="og:description" content="这个常用的COUNT函数，却暗藏着很多玄机，尤其是在面试的时候，一不小心就会被虐。不信的话请尝试回答下以下问题：  1、COUNT有几种用法？ 2、COUNT(字段名)和COUNT(*)的查询结果有什么不同？ 3、COUNT(1)和COUNT(*)之间有什么不同？ 4、COUNT(1)和COUNT(*)之间的效率哪个更高？ 5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*) 6、MyS">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://jameslin23.gitee.io/2020/12/10/mysql之count用法/C:%5CUsers%5CAdministrator.USER-20190627HM%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5Cimage-20201210194250149.png">
<meta property="og:updated_time" content="2020-12-11T02:12:41.323Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mysql之count用法">
<meta name="twitter:description" content="这个常用的COUNT函数，却暗藏着很多玄机，尤其是在面试的时候，一不小心就会被虐。不信的话请尝试回答下以下问题：  1、COUNT有几种用法？ 2、COUNT(字段名)和COUNT(*)的查询结果有什么不同？ 3、COUNT(1)和COUNT(*)之间有什么不同？ 4、COUNT(1)和COUNT(*)之间的效率哪个更高？ 5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*) 6、MyS">
<meta name="twitter:image" content="https://jameslin23.gitee.io/2020/12/10/mysql之count用法/C:%5CUsers%5CAdministrator.USER-20190627HM%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5Cimage-20201210194250149.png">
  
    <link rel="alternate" href="/atom.xml" title="JAVA" type="application/atom+xml">
  

  

  <link rel="icon" href="/css/images/mylogo.jpg">
  <link rel="apple-touch-icon" href="/css/images/mylogo.jpg">
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link href="https://fonts.googleapis.com/css?family=Open+Sans|Montserrat:700" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Roboto:400,300,300italic,400italic" rel="stylesheet" type="text/css">
  <link href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.css" rel="stylesheet">
  <style type="text/css">
    @font-face{font-family:futura-pt; src:url("/css/fonts/FuturaPTBold.otf") format("woff");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt-light; src:url("/css/fonts/FuturaPTBook.otf") format("woff");font-weight:lighter;font-style:normal;}
    @font-face{font-family:futura-pt-italic; src:url("/css/fonts/FuturaPTBookOblique.otf") format("woff");font-weight:400;font-style:italic;}
}

  </style>
  <link rel="stylesheet" href="/css/style.css">

  <script src="/js/jquery-3.1.1.min.js"></script>
  <script src="/js/bootstrap.js"></script>

  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="/css/bootstrap.css">

  
    <link rel="stylesheet" href="/css/dialog.css">
  

  

  
    <link rel="stylesheet" href="/css/header-post.css">
  

  
  
  
    <link rel="stylesheet" href="/css/vdonate.css">
  

</head>
</html>


  <body data-spy="scroll" data-target="#toc" data-offset="50">


  
  <div id="container">
    <div id="wrap">
      
        <header>

    <div id="allheader" class="navbar navbar-default navbar-static-top" role="navigation">
        <div class="navbar-inner">
          
          <div class="container"> 
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
              <span class="sr-only">Toggle navigation</span>
              <span class="icon-bar"></span>
              <span class="icon-bar"></span>
              <span class="icon-bar"></span>
            </button>

            
              <a class="brand" style="border-width: 0;">
                <p>JAVA</p>
              </a>
            
            
            <div class="navbar-collapse collapse">
              <ul class="hnav navbar-nav">
                
                  <li> <a class="main-nav-link" href="/">首页</a> </li>
                
                  <li> <a class="main-nav-link" href="/archives">归档</a> </li>
                
                  <li> <a class="main-nav-link" href="/categories">分类</a> </li>
                
                  <li> <a class="main-nav-link" href="/tags">标签</a> </li>
                
                  <li> <a class="main-nav-link" href="/about">关于</a> </li>
                
                  <li><div id="search-form-wrap">

    <form class="search-form">
        <input type="text" class="ins-search-input search-form-input" placeholder>
        <button type="submit" class="search-form-submit"></button>
    </form>
    <div class="ins-search">
    <div class="ins-search-mask"></div>
    <div class="ins-search-container">
        <div class="ins-input-wrapper">
            <input type="text" class="ins-search-input" placeholder="请输入关键词...">
            <span class="ins-close ins-selectable"><i class="fa fa-times-circle"></i></span>
        </div>
        <div class="ins-section-wrapper">
            <div class="ins-section-container"></div>
        </div>
    </div>
</div>
<script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: '文章',
            PAGES: '页面',
            CATEGORIES: '分类',
            TAGS: '标签',
            UNTITLED: '(无标题)',
        },
        ROOT_URL: '/',
        CONTENT_URL: '/content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>
<script src="/js/insight.js"></script>

</div></li>
            </ul></div>
          </div>
                
      </div>
    </div>

</header>



      
            
      <div id="content" class="outer">
        
          <section id="main" style="float:none;"><article id="post-mysql之count用法" style="width: 75%; float:left;" class="article article-type-post" itemscope itemprop="blogPost">
  <div id="articleInner" class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="thumb" itemprop="name">
      mysql之count用法
    </h1>
  

      </header>
    
    <div class="article-meta">
      
	<a href="/2020/12/10/mysql之count用法/" class="article-date">
	  <time datetime="2020-12-10T11:35:17.000Z" itemprop="datePublished">2020-12-10</time>
	</a>

      
    <a class="article-category-link" href="/categories/数据库/">数据库</a>

      
	<a class="article-views">
	<span id="busuanzi_container_page_pv">
		阅读量<span id="busuanzi_value_page_pv"></span>
	</span>
	</a>

      

    </div>
    <div class="article-entry" itemprop="articleBody">
      
        <p>这个常用的<strong>COUNT</strong>函数，却暗藏着很多玄机，尤其是在面试的时候，一不小心就会被虐。不信的话请尝试回答下以下问题：</p>
<blockquote>
<p>1、COUNT有几种用法？</p>
<p>2、COUNT(字段名)和COUNT(*)的查询结果有什么不同？</p>
<p>3、COUNT(1)和COUNT(*)之间有什么不同？</p>
<p>4、COUNT(1)和COUNT(*)之间的效率哪个更高？</p>
<p>5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*)</p>
<p>6、MySQL的MyISAM引擎对COUNT(*)做了哪些优化？</p>
<p>7、MySQL的InnoDB引擎对COUNT(*)做了哪些优化？</p>
<p>8、上面提到的MySQL对COUNT(*)做的优化，有一个关键的前提是什么？</p>
<p>9、SELECT COUNT(*) 的时候，加不加where条件有差别吗？</p>
<p>10、COUNT(*)、COUNT(1)和COUNT(字段名)的执行过程是怎样的？</p>
</blockquote>
<h3 id="COUNT-列名-、COUNT-常量-和COUNT-之间的区别"><a href="#COUNT-列名-、COUNT-常量-和COUNT-之间的区别" class="headerlink" title="COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别"></a><strong>COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别</strong></h3><p><strong><code>COUNT(常量)</code> 和 <code>COUNT(*)</code>表示的是直接查询符合条件的数据库表的行数。而<code>COUNT(列名)</code>表示的是查询符合条件的列的值不为NULL的行数。</strong></p>
<h3 id="COUNT-的优化"><a href="#COUNT-的优化" class="headerlink" title="COUNT(*)的优化"></a><strong>COUNT(*)的优化</strong></h3><p>前面提到了<code>COUNT(*)</code>是SQL92定义的标准统计行数的语法，所以MySQL数据库对他进行过很多优化。那么，具体都做过哪些事情呢？</p>
<p>这里的介绍要区分不同的执行引擎。MySQL中比较常用的执行引擎就是InnoDB和MyISAM。</p>
<p>MyISAM和InnoDB有很多区别，其中有一个关键的区别和我们接下来要介绍的<code>COUNT(*)</code>有关，那就是<strong>MyISAM不支持事务，MyISAM中的锁是表级锁；**</strong>而InnoDB支持事务，并且支持行级锁。**</p>
<p>因为MyISAM的锁是表级锁，所以同一张表上面的操作需要串行进行，所以，<strong>MyISAM做了一个简单的优化，那就是它可以把表的总行数单独记录下来，如果从一张表中使用COUNT(*)进行查询的时候，可以直接返回这个记录下来的数值就可以了，当然，前提是不能有where条件。</strong></p>
<p>MyISAM之所以可以把表中的总行数记录下来供COUNT(*)查询使用，那是因为MyISAM数据库是表级锁，不会有并发的数据库行数修改，所以查询得到的行数是准确的。</p>
<p>但是，对于InnoDB来说，就不能做这种缓存操作了，因为InnoDB支持事务，其中大部分操作都是行级锁，所以可能表的行数可能会被并发修改，那么缓存记录下来的总行数就不准确了。</p>
<p>但是，InnoDB还是针对COUNT(*)语句做了些优化的。</p>
<p>在InnoDB中，使用COUNT(*)查询行数的时候，不可避免的要进行扫表了，那么，就可以在扫表过程中下功夫来优化效率了。</p>
<p>从MySQL 8.0.13开始，针对InnoDB的<code>SELECT COUNT(*) FROM tbl_name</code>语句，确实在扫表的过程中做了一些优化。前提是查询语句中不包含WHERE或GROUP BY等条件。</p>
<p><strong>我们知道，COUNT(*)的目的只是为了统计总行数，所以，他根本不关心自己查到的具体值，所以，他如果能够在扫表的过程中，选择一个成本较低的索引进行的话，那就可以大大节省时间。</strong></p>
<p>我们知道，InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。</p>
<p>所以，相比之下，非聚簇索引要比聚簇索引小很多，所以<strong>MySQL会优先选择最小的非聚簇索引来扫表。**</strong>所以，当我们建表的时候，除了主键索引以外，创建一个非主键索引还是有必要的。**</p>
<p>至此，我们介绍完了MySQL数据库对于COUNT(*)的优化，这些优化的前提都是查询语句中不包含WHERE以及GROUP BY条件。</p>
<h3 id="COUNT-和COUNT-1"><a href="#COUNT-和COUNT-1" class="headerlink" title="COUNT(*)和COUNT(1)"></a><strong>COUNT(*)和COUNT(1)</strong></h3><p>官方文档</p>
<blockquote>
<p>InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.</p>
</blockquote>
<p><strong>所以，对于COUNT(1)和COUNT(*)，MySQL的优化是完全一样的，根本不存在谁比谁快！</strong></p>
<p>那既然<code>COUNT(*)</code>和<code>COUNT(1)</code>一样，建议用哪个呢？</p>
<p>建议使用<code>COUNT(*)</code>！因为这个是SQL92定义的标准统计行数的语法</p>
<p>《阿里巴巴Java开发手册》中强制要求不让使用 <code>COUNT(列名)</code>或 <code>COUNT(常量)</code>来替代 <code>COUNT(*)</code></p>
<p><img src="/2020/12/10/mysql之count用法/C:%5CUsers%5CAdministrator.USER-20190627HM%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5Cimage-20201210194250149.png" alt="image-20201210194250149"></p>

      
    </div>
    <footer class="article-footer">
      
        <div id="donation_div"></div>

<script src="/js/vdonate.js"></script>
<script>
var a = new Donate({
  title: '如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作!', // 可选参数，打赏标题
  btnText: '打赏支持', // 可选参数，打赏按钮文字
  el: document.getElementById('donation_div'),
  wechatImage: 'https://raw.githubusercontent.com/iTimeTraveler/iTimeTraveler.github.io/site/source/about/donate/images/WeChanQR.png',
  alipayImage: 'https://raw.githubusercontent.com/iTimeTraveler/iTimeTraveler.github.io/site/source/about/donate/images/AliPayQR.jpg'
});
</script>
      
      
      <div>
        <ul class="post-copyright">
          <li class="post-copyright-author">
          <strong>本文作者:  </strong>LeBron Tao
          </li>
          <li class="post-copyright-link">
          <strong>本文链接:  </strong>
          <a href="/2020/12/10/mysql之count用法/" target="_blank" title="mysql之count用法">https://jameslin23.gitee.io/2020/12/10/mysql之count用法/</a>
          </li>
          <li class="post-copyright-license">
            <strong>版权声明:   </strong>
            本博客所有文章除特别声明外，均采用 <a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/" target="_blank" title="Attribution-NonCommercial-NoDerivatives 4.0 International (CC BY-NC-ND 4.0)">CC BY-NC-ND 4.0</a>
            许可协议。转载请注明出处
          </li>
         
        </ul>
<div>
</div></div>
      
      
        
	<div id="comment">
		<!-- 来必力City版安装代码 -->
		<div id="lv-container" data-id="city" data-uid="MTAyMC8yOTQ4MS82MDQ5">
		<script type="text/javascript">
		   (function(d, s) {
		       var j, e = d.getElementsByTagName(s)[0];

		       if (typeof LivereTower === 'function') { return; }

		       j = d.createElement(s);
		       j.src = 'https://cdn-city.livere.com/js/embed.dist.js';
		       j.async = true;

		       e.parentNode.insertBefore(j, e);
		   })(document, 'script');
		</script>
		<noscript>为正常使用来必力评论功能请激活JavaScript</noscript>
		</div>
		<!-- City版安装代码已完成 -->
	</div>



      
      
        
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/面试经典/">面试经典</a></li></ul>

      

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2020/12/11/ZooKeeper/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">上一篇</strong>
      <div class="article-nav-title">
        
          ZooKeeper
        
      </div>
    </a>
  
  
    <a href="/2020/12/09/Memcache/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">下一篇</strong>
      <div class="article-nav-title">Memcache</div>
    </a>
  
</nav>

  
</article>

<!-- Table of Contents -->

  <aside id="toc-sidebar">
    <div id="toc" class="toc-article">
    <strong class="toc-title">文章目录</strong>
    
        <ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#COUNT-列名-、COUNT-常量-和COUNT-之间的区别"><span class="nav-number">1.</span> <span class="nav-text">COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#COUNT-的优化"><span class="nav-number">2.</span> <span class="nav-text">COUNT(*)的优化</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#COUNT-和COUNT-1"><span class="nav-number">3.</span> <span class="nav-text">COUNT(*)和COUNT(1)</span></a></li></ol>
    
    </div>
  </aside>

</section>
        
      </div>
      
      <footer id="footer">
  

  <div class="container">
      	<div class="row">
	      <p> Powered by <a href="http://hexo.io/" target="_blank">Hexo</a> and <a href="https://github.com/iTimeTraveler/hexo-theme-hiker" target="_blank">Hexo-theme-hiker</a> </p>
	      <p id="copyRightEn">Copyright &copy; 2013 - 2021 JAVA All Rights Reserved.</p>
	      
	      
    		<p class="busuanzi_uv">
				访客数 : <span id="busuanzi_value_site_uv"></span> |  
				访问量 : <span id="busuanzi_value_site_pv"></span>
		    </p>
  		   
		</div>

		
  </div>
</footer>


<!-- min height -->

<script>
    var wrapdiv = document.getElementById("wrap");
    var contentdiv = document.getElementById("content");
    var allheader = document.getElementById("allheader");

    wrapdiv.style.minHeight = document.body.offsetHeight + "px";
    if (allheader != null) {
      contentdiv.style.minHeight = document.body.offsetHeight - allheader.offsetHeight - document.getElementById("footer").offsetHeight + "px";
    } else {
      contentdiv.style.minHeight = document.body.offsetHeight - document.getElementById("footer").offsetHeight + "px";
    }
</script>
    </div>
    <!-- <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
    <a href="/categories" class="mobile-nav-link">Categories</a>
  
    <a href="/tags" class="mobile-nav-link">Tags</a>
  
    <a href="/about" class="mobile-nav-link">About</a>
  
</nav> -->
    

<!-- mathjax config similar to math.stackexchange -->

<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    tex2jax: {
      inlineMath: [ ['$','$'], ["\\(","\\)"] ],
      processEscapes: true
    }
  });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
    });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for(i=0; i < all.length; i += 1) {
            all[i].SourceElement().parentNode.className += ' has-jax';
        }
    });
</script>

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/scripts.js"></script>




  <script src="/js/dialog.js"></script>








	<div style="display: none;">
    <script src="https://s95.cnzz.com/z_stat.php?id=1260716016&web_id=1260716016" language="JavaScript"></script>
  </div>



	<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js">
	</script>






  </div>

  <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" style="display: none;">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <h2 class="modal-title" id="myModalLabel">设置</h2>
      </div>
      <hr style="margin-top:0px; margin-bottom:0px; width:80%; border-top: 3px solid #000;">
      <hr style="margin-top:2px; margin-bottom:0px; width:80%; border-top: 1px solid #000;">


      <div class="modal-body">
          <div style="margin:6px;">
            <a data-toggle="collapse" data-parent="#accordion" href="#collapseOne" onclick="javascript:setFontSize();" aria-expanded="true" aria-controls="collapseOne">
              正文字号大小
            </a>
          </div>
          <div id="collapseOne" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingOne">
          <div class="panel-body">
            您已调整页面字体大小
          </div>
        </div>
      


          <div style="margin:6px;">
            <a data-toggle="collapse" data-parent="#accordion" href="#collapseTwo" onclick="javascript:setBackground();" aria-expanded="true" aria-controls="collapseTwo">
              夜间护眼模式
            </a>
        </div>
          <div id="collapseTwo" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingTwo">
          <div class="panel-body">
            夜间模式已经开启，再次单击按钮即可关闭 
          </div>
        </div>

        <div>
            <a data-toggle="collapse" data-parent="#accordion" href="#collapseThree" aria-expanded="true" aria-controls="collapseThree">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;关 于&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</a>
        </div>
         <div id="collapseThree" class="panel-collapse collapse" role="tabpanel" aria-labelledby="headingThree">
          <div class="panel-body">
            JAVA
          </div>
          <div class="panel-body">
            Copyright © 2021 LeBron Tao All Rights Reserved.
          </div>
        </div>
      </div>


      <hr style="margin-top:0px; margin-bottom:0px; width:80%; border-top: 1px solid #000;">
      <hr style="margin-top:2px; margin-bottom:0px; width:80%; border-top: 3px solid #000;">
      <div class="modal-footer">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
      </div>
    </div>
  </div>
</div>
  
  <a id="rocket" href="#top" class=""></a>
  <script type="text/javascript" src="/js/totop.js?v=1.0.0" async=""></script>
  
    <a id="menu-switch"><i class="fa fa-bars fa-lg"></i></a>
  
</body>
</html>